package database;

import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import data.Field;
import data.Image;
import data.Project;
import data.Record;
import data.RecordValue;

public class DataDAO {

	private Database db;

	/**
	 * Constructor used to build the initial DataDAO.
	 * 
	 * @throws ClassNotFoundException
	 *             - thrown if the driver is bad.
	 * @throws SQLException
	 *             - thrown if the SQL is bad.
	 */
	public DataDAO(Database in_db) throws ClassNotFoundException, SQLException {
		db = in_db;
	}

	/**
	 * Gets all of the projects in the database and loads them into memory
	 * 
	 * @return Returns an ArrayList of Project objects
	 * @throws SQLException
	 */
	public ArrayList<Project> getAllProjects() throws SQLException {
		ArrayList<Project> allProjects = new ArrayList<Project>();
		String sql = "SELECT * FROM projects";
		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		ResultSet rs = pStmt.executeQuery();

		while (rs.next()) {
			int projectID = rs.getInt("projectID");
			String projTitle = rs.getString("projectTitle");
			int recordsPerImage = rs.getInt("recordsPerImage");
			int firstYCoord = rs.getInt("firstYCoord");
			int recordHeight = rs.getInt("recordHeight");
			int numOfFields = rs.getInt("numOfFields");

			Project tmpProj = new Project(projectID, projTitle,
					recordsPerImage, firstYCoord, recordHeight, numOfFields);

			allProjects.add(tmpProj);
		}

		return allProjects;

	}

	/**
	 * Creates a new row in the Project table.
	 * 
	 * @param projectID
	 *            new Project information.
	 * @param recordsPerPage
	 *            new Project information.
	 * @param firstYCoord
	 *            new Project information.
	 * @param recordHeight
	 *            new Project information.
	 * @param numOfFields
	 *            new Project information.
	 * @throws SQLException
	 */
	public void createProject(int projectID, String projectTitle,
			int recordsPerImage, int firstYCoord, int recordHeight,
			int numOfFields) throws SQLException {

		String sql = "INSERT INTO projects (projectID, projectTitle, recordsPerImage, firstYCoord, recordHeight, numOfFields)"
				+ "VALUES(?, ?, ?, ?, ?, ?)";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, projectID);
		pStmt.setString(2, projectTitle);
		pStmt.setInt(3, recordsPerImage);
		pStmt.setInt(4, firstYCoord);
		pStmt.setInt(5, recordHeight);
		pStmt.setInt(6, numOfFields);

		pStmt.executeUpdate();

	}

	/**
	 * Reads a row from the Project table.
	 * 
	 * @param in_projectID
	 *            - used to identify the appropriate project.
	 * @return returns a Project object.
	 * @throws SQLException
	 */
	public Project readProject(int in_projectID) throws SQLException {

		String sql = "SELECT * FROM projects WHERE projectID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, in_projectID);

		ResultSet rs = pStmt.executeQuery();

		int projectID = rs.getInt("projectID");
		String projTitle = rs.getString("projectTitle");
		int recordsPerImage = rs.getInt("recordsPerImage");
		int firstYCoord = rs.getInt("firstYCoord");
		int recordHeight = rs.getInt("recordHeight");
		int numOfFields = rs.getInt("numOfFields");

		Project tmpProj = new Project(projectID, projTitle, recordsPerImage,
				firstYCoord, recordHeight, numOfFields);

		return tmpProj;
	}

	/**
	 * Updates a row in the Project table.
	 * 
	 * @param newProject
	 *            matches the Project object with appropriate data in the
	 *            database.
	 * @throws SQLException
	 */
	public void updateProject(Project proj) throws SQLException {

		String sql = "UPDATE projects SET projectID = ?, projectTitle = ?, "
				+ "recordsPerImage = ?, firstYCoord = ?, recordHeight = ?, numOfFields = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, proj.getProjectID());
		pStmt.setString(2, proj.getProjectTitle());
		pStmt.setInt(3, proj.getRecordsPerImage());
		pStmt.setInt(4, proj.getFirstYCoord());
		pStmt.setInt(5, proj.getRecordHeight());
		pStmt.setInt(6, proj.getNumOfFields());

		pStmt.executeUpdate();

	}

	/**
	 * Drops a row from the Project table.
	 * 
	 * @param projectID
	 *            removes the row in the database related to the projectID.
	 * @throws SQLException
	 */
	public void dropProject(int projectID) throws SQLException {

		String sql = "DELETE FROM projects WHERE projectID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, projectID);

		pStmt.executeUpdate();

	}

	/**
	 * Gets all of the fields in the database and loads them into memory
	 * 
	 * @return Returns an ArrayList of Field objects
	 * @throws SQLException
	 */
	public ArrayList<Field> getAllFields() throws SQLException {
		ArrayList<Field> allFields = new ArrayList<Field>();
		String sql = "SELECT * FROM fields";
		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		ResultSet rs = pStmt.executeQuery();

		while (rs.next()) {
			int fieldID = rs.getInt("fieldID");
			int projectID = rs.getInt("projectID");
			String title = rs.getString("title");
			int xCoord = rs.getInt("xCoord");
			int width = rs.getInt("width");
			String helphtml = rs.getString("helphtml");
			String knowndata = rs.getString("knowndata");

			Field tmpField = new Field(fieldID, projectID, title, xCoord,
					width, helphtml, knowndata);

			allFields.add(tmpField);
		}

		return allFields;
	}

	/**
	 * Gets all of the fields according to the field's projectID and loads them
	 * into memory
	 * 
	 * @param in_projectID
	 *            tells the database which fields to draw based on the projectID
	 * 
	 * @return Returns an ArrayList of Field objects
	 * @throws SQLException
	 */
	public ArrayList<Field> getAllFields(int in_projectID) throws SQLException {
		ArrayList<Field> allFields = new ArrayList<Field>();
		String sql = "SELECT * FROM fields WHERE projectID = ?";
		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, in_projectID);

		ResultSet rs = pStmt.executeQuery();

		while (rs.next()) {
			int fieldID = rs.getInt("fieldID");
			int projectID = rs.getInt("projectID");
			String title = rs.getString("title");
			int xCoord = rs.getInt("xCoord");
			int width = rs.getInt("width");
			String helphtml = rs.getString("helphtml");
			String knowndata = rs.getString("knowndata");

			Field tmpField = new Field(fieldID, projectID, title, xCoord,
					width, helphtml, knowndata);

			allFields.add(tmpField);
		}

		return allFields;
	}

	/**
	 * Creates a new Field row in the database.
	 * 
	 * @param fieldID
	 *            new Field information.
	 * @param projectID
	 *            new Field information.
	 * @param title
	 *            new Field information.
	 * @param xCoord
	 *            new Field information.
	 * @param width
	 *            new Field information.
	 * @param helphtml
	 *            new Field information.
	 * @throws SQLException
	 */
	public void createField(int fieldID, int projectID, String title,
			int xCoord, int width, String helphtml) throws SQLException {

		String sql = "INSERT INTO fields (fieldID, projectID, title, xCoord, width, helphtml)"
				+ "VALUES(?, ?, ?, ?, ?, ?)";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, fieldID);
		pStmt.setInt(2, projectID);
		pStmt.setString(3, title);
		pStmt.setInt(4, xCoord);
		pStmt.setInt(5, width);
		pStmt.setString(6, helphtml);

		pStmt.executeUpdate();

	}

	/**
	 * Reads the given row from the database.
	 * 
	 * @param fieldID
	 *            used to identify the appropriate row in the database.
	 * @param in_projID
	 *            used to identify the appropriate row in the database.
	 * @return returns a Field object according to the fieldID and projectID.
	 * @throws SQLException
	 */
	public Field readField(int in_fID, int in_projID) throws SQLException {

		String sql = "SELECT * FROM fields WHERE fieldID = ? AND projectID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, in_fID);
		pStmt.setInt(2, in_projID);

		ResultSet rs = pStmt.executeQuery();

		int fieldID = rs.getInt("fieldID");
		int projectID = rs.getInt("projectID");
		String title = rs.getString("title");
		int xCoord = rs.getInt("xCoord");
		int width = rs.getInt("width");
		String helphtml = rs.getString("helphtml");
		String knowndata = rs.getString("knowndata");

		Field tmpField = new Field(fieldID, projectID, title, xCoord, width,
				helphtml, knowndata);

		return tmpField;
	}

	/**
	 * Updates a row in the Field table.
	 * 
	 * @param field
	 *            used to update the data in the database.
	 * @throws SQLException
	 */
	public void updateField(Field field) throws SQLException {

		String sql = "UPDATE fields SET fieldID = ?, projectID = ?, "
				+ "title = ?, xCoord = ?, width = ?, helphtml = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, field.getFieldID());
		pStmt.setInt(2, field.getProjectID());
		pStmt.setString(3, field.getTitle());
		pStmt.setInt(4, field.getxCoord());
		pStmt.setInt(5, field.getWidth());
		pStmt.setString(6, field.getHelphtml());

		pStmt.executeUpdate();

	}

	/**
	 * Removes the Field row from the database.
	 * 
	 * @param fieldID
	 *            used to identify the appropriate row in the database.
	 * @param projectID
	 *            used to identify the appropriate row in the database.
	 * @throws SQLException
	 */
	public void dropField(int fieldID, int projectID) throws SQLException {

		String sql = "DELETE FROM fields WHERE fieldID = ? AND projectID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, fieldID);
		pStmt.setInt(2, projectID);

		pStmt.executeUpdate();

	}

	/**
	 * Gets all of the images in the database and loads them into memory
	 * 
	 * @return Returns an ArrayList of Image objects
	 * @throws SQLException
	 */
	public ArrayList<Image> getAllImages() throws SQLException {
		ArrayList<Image> allImages = new ArrayList<Image>();
		String sql = "SELECT * FROM images";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		ResultSet rs = pStmt.executeQuery();

		while (rs.next()) {
			int imageID = rs.getInt("imageID");
			int projectID = rs.getInt("projectID");
			String fileURL = rs.getString("file");
			Image tmpImage = new Image(imageID, projectID, fileURL);
			allImages.add(tmpImage);
		}

		return allImages;
	}

	/**
	 * Gets all of the images related to the projectID and loads them into
	 * memory
	 * 
	 * @param in_projectID
	 *            the projectID related to the images
	 * 
	 * @return Returns an ArrayList of Image objects
	 * @throws SQLException
	 */
	public ArrayList<Image> getAllImages(int in_projectID) throws SQLException {
		ArrayList<Image> allImages = new ArrayList<Image>();

		String sql = "SELECT * FROM images WHERE projectID = ?";
		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, in_projectID);

		ResultSet rs = pStmt.executeQuery();

		while (rs.next()) {
			int imageID = rs.getInt("imageID");
			int projectID = rs.getInt("projectID");
			String fileURL = rs.getString("file");
			Image tmpImage = new Image(imageID, projectID, fileURL);
			allImages.add(tmpImage);
		}

		return allImages;
	}

	/**
	 * Creates a new row in the Image table.
	 * 
	 * @param in_imageID
	 *            used to identify the appropriate row in the database.
	 * @param in_projectID
	 *            used to identify the appropriate row in the database.
	 * @param file
	 *            used to identify the appropriate row in the database.
	 * @throws SQLException
	 */
	public void createImage(int in_imageID, int in_projectID, String in_file)
			throws SQLException {

		String sql = "INSERT INTO images (imageID, projectID, file)"
				+ "VALUES(?, ?, ?)";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, in_imageID);
		pStmt.setInt(2, in_projectID);
		pStmt.setString(3, in_file);

		pStmt.executeUpdate();

	}

	/**
	 * Reads a row from the Image table.
	 * 
	 * @param imageID
	 *            used to identify the appropriate row in the database.
	 * @param projectID
	 *            used to identify the appropriate row in the database.
	 * @return returns an object version of the row in the database.
	 * @throws SQLException
	 */
	public Image readImage(int in_imageID, int in_projectID)
			throws SQLException {

		String sql = "SELECT * FROM images WHERE imageID = ? AND projectID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, in_imageID);
		pStmt.setInt(2, in_projectID);

		ResultSet rs = pStmt.executeQuery();
		if (rs.next()) {
			int imageID = rs.getInt("imageID");
			int projectID = rs.getInt("projectID");
			String fileURL = rs.getString("file");
			Image tmpImage = new Image(imageID, projectID, fileURL);
			return tmpImage;
		} else {
			return new Image();
		}
	}

	/**
	 * Updates a row in the Image table.
	 * 
	 * @param image
	 *            used to identify the appropriate row in the database.
	 * @throws SQLException
	 */
	public void updateImage(Image image) throws SQLException {

		String sql = "UPDATE images SET imageID = ?, projectID = ?, file = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, image.getImageID());
		pStmt.setInt(2, image.getProjectID());
		pStmt.setString(3, image.getFileURL());

		pStmt.executeUpdate();

	}

	/**
	 * Drops a row from the Image table
	 * 
	 * @param imageID
	 *            used to identify the appropriate row in the database.
	 * @param projectID
	 *            used to identify the appropriate row in the database.
	 * @throws SQLException
	 */
	public void dropImage(int imageID, int projectID) throws SQLException {

		String sql = "DELETE FROM images WHERE imageID = ? AND projectID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, imageID);
		pStmt.setInt(2, projectID);

		pStmt.executeUpdate();

	}

	/**
	 * Gets all of the records in the database and loads them into memory
	 * 
	 * @return Returns an ArrayList of Record objects
	 * @throws SQLException
	 */
	public ArrayList<Record> getAllRecords() throws SQLException {
		ArrayList<Record> allRecords = new ArrayList<Record>();

		String sql = "SELECT * FROM records";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		ResultSet rs = pStmt.executeQuery();

		while (rs.next()) {
			int recordID = rs.getInt("recordID");
			int imageID = rs.getInt("imageID");

			Record tmpRec = new Record(recordID, imageID);
			allRecords.add(tmpRec);
		}

		return allRecords;
	}

	/**
	 * Gets all of the records related to a specific imageID and loads them into
	 * memory.
	 * 
	 * @param in_imageID
	 *            the imageID required to find the records.
	 * @return Returns an ArrayList of Record objects.
	 * @throws SQLException
	 */
	public ArrayList<Record> getAllRecords(int in_imageID) throws SQLException {
		ArrayList<Record> allRecords = new ArrayList<Record>();

		String sql = "SELECT * FROM records WHERE imageID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, in_imageID);

		ResultSet rs = pStmt.executeQuery();

		while (rs.next()) {
			int recordID = rs.getInt("recordID");
			int imageID = rs.getInt("imageID");
			Record tmpRec = new Record(recordID, imageID);
			allRecords.add(tmpRec);
		}

		return allRecords;
	}

	/**
	 * Creates a new row in the Record table.
	 * 
	 * @param recordID
	 *            new Record information.
	 * @param imageID
	 *            new Record information.
	 * @throws SQLException
	 */
	public void createRecord(int recordID, int imageID) throws SQLException {

		String sql = "INSERT INTO records (recordID, imageID)" + "VALUES(?, ?)";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, recordID);
		pStmt.setInt(2, imageID);

		pStmt.execute();

	}

	/**
	 * Reads a row from the Record table.
	 * 
	 * @param in_recID
	 *            used to identify the appropriate row in the database.
	 * @param in_imgID
	 *            used to identify the appropriate row in the database.
	 * @return returns a Record object version of the Record table row.
	 * @throws SQLException
	 */
	public Record readRecord(int in_recID, int in_imgID) throws SQLException {

		String sql = "SELECT * FROM records WHERE recordID = ? AND imageID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, in_recID);
		pStmt.setInt(2, in_imgID);

		ResultSet rs = pStmt.executeQuery();

		int recordID = rs.getInt("recordID");
		int imageID = rs.getInt("imageID");

		Record tmpRec = new Record(recordID, imageID);

		return tmpRec;
	}

	/**
	 * Updates a row in the Record table.
	 * 
	 * @param record
	 *            used to identify the appropriate row in the database.
	 * @throws SQLException
	 */
	public void updateRecord(Record record) throws SQLException {

		String sql = "UPDATE records SET recordID = ?, imageID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, record.getRecordID());
		pStmt.setInt(2, record.getImageID());

		pStmt.executeUpdate();
	}

	/**
	 * Drops a row from the Record table.
	 * 
	 * @param recordID
	 *            used to identify the appropriate row in the database.
	 * @param imageID
	 *            used to identify the appropriate row in the database.
	 * @throws SQLException
	 */
	public void dropRecord(int recordID, int imageID) throws SQLException {

		String sql = "DELETE FROM records WHERE recordID = ? AND imageID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, recordID);
		pStmt.setInt(2, imageID);

		pStmt.executeUpdate();

	}

	/**
	 * Gets all of the record values in the database and loads them into memory
	 * 
	 * @return Returns an ArrayList of RecordValue objects
	 * @throws SQLException
	 */
	public ArrayList<RecordValue> getAllRecordValues() throws SQLException {
		ArrayList<RecordValue> allRecVals = new ArrayList<RecordValue>();
		String sql = "SELECT * FROM recordValues";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		ResultSet rs = pStmt.executeQuery();
		while (rs.next()) {
			int valueID = rs.getInt("valueID");
			int recordID = rs.getInt("recordID");
			String valString = rs.getString("valueString");
			int fieldID = rs.getInt("fieldID");

			RecordValue tmpRecVal = new RecordValue(valueID, recordID,
					valString, fieldID);

			allRecVals.add(tmpRecVal);
		}

		return allRecVals;
	}

	/**
	 * Gets all of the record values in the database and loads them into memory
	 * 
	 * @return Returns an ArrayList of RecordValue objects
	 * @throws SQLException
	 */
	public ArrayList<RecordValue> getAllRecordValues(int in_recordID)
			throws SQLException {
		ArrayList<RecordValue> allRecVals = new ArrayList<RecordValue>();
		String sql = "SELECT * FROM recordValues WHERE recordID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);
		pStmt.setInt(1, in_recordID);

		ResultSet rs = pStmt.executeQuery();
		while (rs.next()) {
			int valueID = rs.getInt("valueID");
			int recordID = rs.getInt("recordID");
			String valString = rs.getString("valueString");
			int fieldID = rs.getInt("fieldID");

			RecordValue tmpRecVal = new RecordValue(valueID, recordID,
					valString, fieldID);

			allRecVals.add(tmpRecVal);
		}

		return allRecVals;
	}

	/**
	 * Creates a new row in the RecordValue table.
	 * 
	 * @param valueID
	 *            new RecordValue information.
	 * @param valueString
	 *            new RecordValue information.
	 * @param valueIdentifier
	 *            new RecordValue information.
	 * @param recordID
	 *            new RecordValue information.
	 * @throws SQLException
	 */
	public void createRecordValue(int valueID, String valueString,
			int recordID, int fieldID) throws SQLException {

		String sql = "INSERT INTO recordValues (valueID, valueString, recordID, fieldID)"
				+ "VALUES(?, ?, ?, ?)";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, valueID);
		pStmt.setString(2, valueString);
		pStmt.setInt(3, recordID);
		pStmt.setInt(4, fieldID);

		pStmt.execute();

	}

	/**
	 * Reads a row from the RecordValue table.
	 * 
	 * @param in_valID
	 *            used to identify the appropriate row in the database.
	 * @param in_recID
	 *            used to identify the appropriate row in the database.
	 * @param in_fieldID
	 *            used to identify the appropriate row in the database.
	 * @return returns a RecordValue object version of the row in RecordValue
	 *         table.
	 * @throws SQLException
	 */
	public RecordValue readRecordValue(int in_valID, int in_recID,
			int in_fieldID) throws SQLException {

		String sql = "SELECT * FROM recordValues WHERE valueID = ? AND recordID = ? AND fieldID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, in_valID);
		pStmt.setInt(2, in_recID);
		pStmt.setInt(3, in_fieldID);

		ResultSet rs = pStmt.executeQuery();

		int valueID = rs.getInt("valueID");
		int recordID = rs.getInt("recordID");
		String valString = rs.getString("valueString");
		int fieldID = rs.getInt("fieldID");

		RecordValue tmpRecVal = new RecordValue(valueID, recordID, valString,
				fieldID);

		return tmpRecVal;
	}

	/**
	 * Updates a row in the RecordValue table.
	 * 
	 * @param newRecordValue
	 *            used to identify the appropriate row in the database.
	 * @throws SQLException
	 */
	public void updateRecordValue(RecordValue recordValue) throws SQLException {

		String sql = "UPDATE recordValues SET valueID = ?, valueString = ?, recordID = ?, fieldID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, recordValue.getRecordID());
		pStmt.setString(2, recordValue.getValueString());
		pStmt.setInt(3, recordValue.getRecordID());
		pStmt.setInt(4, recordValue.getFieldID());

		pStmt.executeUpdate();

	}

	/**
	 * Drops a row in the RecordValue table.
	 * 
	 * @param valueID
	 *            used to identify the appropriate row in the database.
	 * @param recordID
	 *            used to identify the appropriate row in the database.
	 * @param fieldID
	 *            used to identify the appropriate row in the database.
	 * @throws SQLException
	 */
	public void dropRecordValue(int valueID, int recordID, int fieldID)
			throws SQLException {

		String sql = "DELETE FROM recordValues WHERE valueID = ? AND recordID = ? AND fieldID = ?";

		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);

		pStmt.setInt(1, fieldID);
		pStmt.setInt(2, recordID);
		pStmt.setInt(3, fieldID);

		pStmt.executeUpdate();
	}

	public void buildTables() throws SQLException {
		String sql = "DROP TABLE IF EXISTS users;";
		PreparedStatement pStmt = db.getConnection().prepareStatement(sql);
		pStmt.execute();
		sql = "DROP TABLE IF EXISTS projects;";
		pStmt = db.getConnection().prepareStatement(sql);
		pStmt.execute();
		sql = "DROP TABLE IF EXISTS fields;";
		pStmt = db.getConnection().prepareStatement(sql);
		pStmt.execute();
		sql = "DROP TABLE IF EXISTS images;";
		pStmt = db.getConnection().prepareStatement(sql);
		pStmt.execute();
		sql = "DROP TABLE IF EXISTS records;";
		pStmt = db.getConnection().prepareStatement(sql);
		pStmt.execute();
		sql = "DROP TABLE IF EXISTS recordValues;";
		pStmt = db.getConnection().prepareStatement(sql);
		pStmt.execute();
		sql = "CREATE TABLE projects(projectID integer primary key unique not null, projectTitle text, recordsPerImage integer, firstYCoord integer, recordHeight integer, numOfFields integer);";
		pStmt = db.getConnection().prepareStatement(sql);
		pStmt.execute();
		sql = "CREATE TABLE fields(fieldID integer primary key unique not null, projectID integer not null, title text, xCoord integer, width integer, helphtml text, knowndata text, FOREIGN KEY (projectID) REFERENCES projects(projectID));";
		pStmt = db.getConnection().prepareStatement(sql);
		pStmt.execute();
		sql = "CREATE TABLE images(imageID integer primary key unique not null, projectID integer not null, file text, FOREIGN KEY (projectID) REFERENCES projects(projectID));";
		pStmt = db.getConnection().prepareStatement(sql);
		pStmt.execute();
		sql = "CREATE TABLE records(recordID integer primary key unique not null, imageID integer not null, FOREIGN KEY (imageID) REFERENCES images(imageID));";
		pStmt = db.getConnection().prepareStatement(sql);
		pStmt.execute();
		sql = "CREATE TABLE recordValues(valueID integer primary key unique not null, valueString text, valueIdentifier text, recordID integer not null, fieldID integer not null, FOREIGN KEY (recordID) REFERENCES records(recordID), FOREIGN KEY (fieldID) REFERENCES fields(fieldID));";
		pStmt = db.getConnection().prepareStatement(sql);
		pStmt.execute();
		sql = "CREATE TABLE users(username text primary key unique not null, password text, firstName text, lastName text, email text, indexedRecords integer);";
		pStmt = db.getConnection().prepareStatement(sql);
		pStmt.execute();
	}
}
